﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;


public partial class Main_Reports_Accounts_BalanceSheet : System.Web.UI.Page
{
    Datautility DU = new Datautility();
    string strSql = string.Empty;
    double dblTotalExp = 0;
    double dblTotalIncome = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["userid"] == null)
        {
            Response.Redirect("~/SessionExpired.aspx");
        }
        if (!IsPostBack)
        {
            hidFinyear.Value = Session["FINYEAR"].ToString();
            string strFromDate = "01/04/" + Session["FINYEAR"].ToString();
            string strToDate = "31/03/" + (int.Parse(Session["FINYEAR"].ToString()) + 1).ToString();
            _GetBalanceSheet(strFromDate, strToDate);
        }
    }
    protected void btnCangeDate_Click(object sender, EventArgs e)
    {
        _GetBalanceSheet(txtFromDate.Text, txtToDate.Text);
    }
    private void _GetBalanceSheet(string strFromDate, string strToDate)
    {

        if (strFromDate != string.Empty && strToDate != string.Empty)
        {
            lblCompanyName.Text = Session["compname"].ToString();
            lblHeading.Text = "Balance Sheet from " + Convert.ToDateTime(strFromDate).ToString("dd-MMM-yyyy") + " To " + Convert.ToDateTime(strToDate).ToString("dd-MMM-yyyy") + " .";
            string[] arrStr = strFromDate.ToString().Split('/');
            strFromDate = arrStr[1] + "/" + arrStr[0] + "/" + arrStr[2];
            arrStr = strToDate.ToString().Split('/');
            strToDate = arrStr[1] + "/" + arrStr[0] + "/" + arrStr[2];



            strSql = "select GID,GNAME  from ACC_GRPMAST where PID=0 and gid<>pid";
            DataTable dtLiability = DU.getdata(strSql);

            strSql = "select GID,GNAME  from ACC_GRPMAST where PID=1 and gid<>pid";
            DataTable dtAssets = DU.getdata(strSql);

            int totalRowCount = 0;
            if (dtLiability.Rows.Count > dtAssets.Rows.Count)
            {
                totalRowCount = dtLiability.Rows.Count;
            }
            else
            {
                totalRowCount = dtAssets.Rows.Count;
            }

            DataTable DTMain = new DataTable();
            double dblExp = 0;
            double dblIncome = 0;
            DataColumn id = new DataColumn("id", typeof(int));
            id.AutoIncrement = true;
            DTMain.Columns.Add(id);
            DTMain.Columns.Add("LiabilityGroup", typeof(string));
            DTMain.Columns.Add("LiabilityTotal", typeof(string));
            DTMain.Columns.Add("AssetsGroup", typeof(string));
            DTMain.Columns.Add("AssetsTotal", typeof(string));


            for (int i = 0; i < totalRowCount; i++)
            {
                string strProfitAndLoss = string.Empty;
                string strProfitAndLossAmt = string.Empty;
                string strExpGid = string.Empty;
                string strLiabilityGName = string.Empty;
                string strLiabilityTotal = string.Empty;
                string strIncGid = string.Empty;
                string strAssetsGName = string.Empty;
                string strAssetsTotal = string.Empty;
                DataRow DRMain = DTMain.NewRow();
                if (i < dtLiability.Rows.Count)
                {
                    strExpGid = dtLiability.Rows[i]["Gid"].ToString();
                    strLiabilityGName = dtLiability.Rows[i]["GNAME"].ToString();

                }
                if (i < dtAssets.Rows.Count)
                {
                    strIncGid = dtAssets.Rows[i]["gid"].ToString();
                    strAssetsGName = dtAssets.Rows[i]["GNAME"].ToString();
                }
                if (strExpGid != string.Empty)
                {
                    SqlParameter[] sparam ={
                                           new SqlParameter("@FROMDATE",strFromDate),
                                           new SqlParameter("@TODATE",strToDate),
                                           new SqlParameter("@GID",strExpGid),
                                           new SqlParameter("@INCLUDEOPTVOUCHERS","1"),
                                           new SqlParameter("@INCLUDESUBGROUPS","1"),
                                           new SqlParameter("@TOTALTYPE","2"),
                                           new SqlParameter("@FINYEAR",Session["FINYEAR"].ToString()),
                                           new SqlParameter("@ISFIRSTYEAR",_IsFirstYear(Session["FINYEAR"].ToString()))
                                       };
                    DataTable DT = DU.getdatausingprocedure("USP_ACC_GetGroupTotal", sparam);
                    if (DT.Rows.Count > 0)
                    {
                        if (DT.Rows[0][0].ToString() != string.Empty)
                        {
                            if (Math.Abs(Convert.ToDouble(DT.Rows[0][0].ToString())) > 0)
                            {
                                strLiabilityTotal = Convert.ToDouble(string.Format("{0:0.00}", Math.Abs(Convert.ToDouble(DT.Rows[0][0].ToString())))).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "");
                                
                            }

                        }
                    }
                }
                if (strIncGid != string.Empty)
                {
                    SqlParameter[] sparam ={
                                           new SqlParameter("@FROMDATE",strFromDate),
                                           new SqlParameter("@TODATE",strToDate),
                                           new SqlParameter("@GID",strIncGid),
                                           new SqlParameter("@INCLUDEOPTVOUCHERS","1"),
                                           new SqlParameter("@INCLUDESUBGROUPS","1"),
                                           new SqlParameter("@TOTALTYPE","2"),
                                           new SqlParameter("@FINYEAR",Session["FINYEAR"].ToString()),
                                           new SqlParameter("@ISFIRSTYEAR",_IsFirstYear(Session["FINYEAR"].ToString()))
                                       };
                    DataTable DT = DU.getdatausingprocedure("USP_ACC_GetGroupTotal", sparam);
                    if (DT.Rows.Count > 0)
                    {
                        if (DT.Rows[0][0].ToString() != string.Empty)
                        {
                            if (Math.Abs(Convert.ToDouble(DT.Rows[0][0].ToString())) > 0)
                            {
                                strAssetsTotal = Convert.ToDouble(string.Format("{0:0.00}", Math.Abs(Convert.ToDouble(DT.Rows[0][0].ToString())))).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "");
                                
                            }
                        }
                    }
                }

                DRMain["LiabilityGroup"] = strLiabilityGName.ToString();
                DRMain["LiabilityTotal"] = strLiabilityTotal.ToString();
                DRMain["AssetsGroup"] = strAssetsGName.ToString();
                DRMain["AssetsTotal"] = strAssetsTotal.ToString();
                DTMain.Rows.Add(DRMain);
            }

            dtLiability.Rows.Clear();
            dtAssets.Rows.Clear();
            dtLiability = DTMain;
            dtAssets = DTMain;
            DataView dvExp = dtLiability.DefaultView;
            dvExp.RowFilter = "LiabilityTotal<>''";
            dtLiability = dvExp.ToTable();


            DataView dvInc = dtAssets.DefaultView;
            dvInc.RowFilter = "AssetsTotal<>''";
            dtAssets = dvInc.ToTable();


            totalRowCount = 0;
            if (dtLiability.Rows.Count > dtAssets.Rows.Count)
            {
                totalRowCount = dtLiability.Rows.Count;
            }
            else
            {
                totalRowCount = dtAssets.Rows.Count;
            }

            DataTable DTMain1 = new DataTable();
            DTMain1.Columns.Add("LiabilityGroup", typeof(string));
            DTMain1.Columns.Add("LiabilityTotal", typeof(string));
            DTMain1.Columns.Add("AssetsGroup", typeof(string));
            DTMain1.Columns.Add("AssetsTotal", typeof(string));

            for (int i = 0; i < totalRowCount; i++)
            {
                DataRow DRMain = DTMain1.NewRow();
                string strLiabilityGName = string.Empty;
                string strLiabilityTotal = string.Empty;
                string strAssetsGName = string.Empty;
                string strAssetsTotal = string.Empty;

                if (i < dtLiability.Rows.Count)
                {
                    strLiabilityGName = dtLiability.Rows[i]["LiabilityGroup"].ToString();
                    strLiabilityTotal = dtLiability.Rows[i]["LiabilityTotal"].ToString();

                }
                if (i < dtAssets.Rows.Count)
                {
                    strAssetsGName = dtAssets.Rows[i]["AssetsGroup"].ToString();
                    strAssetsTotal = dtAssets.Rows[i]["AssetsTotal"].ToString();
                }
                else
                {
                    strAssetsGName = "";
                    strAssetsTotal = "";
                }

                DRMain["LiabilityGroup"] = strLiabilityGName.ToString();
                DRMain["LiabilityTotal"] = strLiabilityTotal.ToString();
                DRMain["AssetsGroup"] = strAssetsGName.ToString();
                DRMain["AssetsTotal"] = strAssetsTotal.ToString();
                DTMain1.Rows.Add(DRMain);
            }

            dblExp = 0;
            dblIncome = 0;

            SqlParameter[] sparam1 ={
                                           new SqlParameter("@FROMDATE",strFromDate),
                                           new SqlParameter("@TODATE",strToDate),
                                           new SqlParameter("@GID","2"),
                                           new SqlParameter("@INCLUDEOPTVOUCHERS","1"),
                                           new SqlParameter("@INCLUDESUBGROUPS","1"),
                                           new SqlParameter("@TOTALTYPE","2"),
                                           new SqlParameter("@FINYEAR",Session["FINYEAR"].ToString()),
                                           new SqlParameter("@ISFIRSTYEAR",_IsFirstYear(Session["FINYEAR"].ToString()))
                                       };
            DataTable DTExp = DU.getdatausingprocedure("USP_ACC_GetGroupTotal", sparam1);
            if (DTExp.Rows.Count > 0)
            {
                if (DTExp.Rows[0][0].ToString() != string.Empty)
                {
                    if (Math.Abs(Convert.ToDouble(DTExp.Rows[0][0].ToString())) > 0)
                    {
                        dblExp = Math.Abs(Convert.ToDouble(DTExp.Rows[0][0].ToString()));
                    }
                }
            }
            SqlParameter[] sparam2 ={
                                           new SqlParameter("@FROMDATE",strFromDate),
                                           new SqlParameter("@TODATE",strToDate),
                                           new SqlParameter("@GID","3"),
                                           new SqlParameter("@INCLUDEOPTVOUCHERS","1"),
                                           new SqlParameter("@INCLUDESUBGROUPS","1"),
                                           new SqlParameter("@TOTALTYPE","2"),
                                           new SqlParameter("@FINYEAR",Session["FINYEAR"].ToString()),
                                           new SqlParameter("@ISFIRSTYEAR",_IsFirstYear(Session["FINYEAR"].ToString()))
                                       };
            DataTable DTInc = DU.getdatausingprocedure("USP_ACC_GetGroupTotal", sparam2);
            if (DTInc.Rows.Count > 0)
            {
                if (DTInc.Rows[0][0].ToString() != string.Empty)
                {
                    if (Math.Abs(Convert.ToDouble(DTInc.Rows[0][0].ToString())) > 0)
                    {
                        dblIncome = Math.Abs(Convert.ToDouble(DTInc.Rows[0][0].ToString()));
                    }
                }
            }

            DataRow DRMain1 = DTMain1.NewRow();
            if (dblExp > dblIncome)
            {
                DRMain1["AssetsGroup"] = "Profit & Loss A/c";
                DRMain1["AssetsTotal"] = Convert.ToDouble(string.Format("{0:0.00}", Convert.ToDouble(dblExp - dblIncome))).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "");
            }
            else if (dblExp < dblIncome)
            {
                DRMain1["LiabilityGroup"] = "Profit & Loss A/c";
                DRMain1["LiabilityTotal"] = Convert.ToDouble(string.Format("{0:0.00}", Convert.ToDouble(dblIncome - dblExp))).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "");
            }
            else
            {
                DRMain1["LiabilityGroup"] = "";
                DRMain1["LiabilityTotal"] = "";
                DRMain1["AssetsGroup"] = "";
                DRMain1["AssetsTotal"] = "";
            }
            DTMain1.Rows.Add(DRMain1);


            grdBalanceSheet.DataSource = DTMain1; ;
            grdBalanceSheet.DataBind();


        }
    }
    protected void grdBalanceSheet_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            e.Row.Cells[0].Attributes.Add("colspan", "2");
            e.Row.Cells.RemoveAt(1);
            e.Row.Cells[0].Text = "Liabilities";
            e.Row.Cells[0].Style.Add("border-right", "solid 1px #d6d3ce");
            e.Row.Cells[1].Attributes.Add("colspan", "2");
            e.Row.Cells.RemoveAt(2);
            e.Row.Cells[1].Text = "Assets";
            e.Row.Style.Add("border-bottom", "solid 1px #d6d3ce");
            e.Row.Cells[0].Style.Add("border-bottom", "solid 1px #d6d3ce");
            e.Row.Cells[1].Style.Add("border-bottom", "solid 1px #d6d3ce");
            e.Row.HorizontalAlign = HorizontalAlign.Left;
        }
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            if (e.Row.Cells[1].Text != string.Empty && e.Row.Cells[1].Text != "&nbsp;")
            {
                dblTotalExp += Convert.ToDouble(e.Row.Cells[1].Text);
            }
            if (e.Row.Cells[3].Text != string.Empty && e.Row.Cells[3].Text != "&nbsp;")
            {
                dblTotalIncome += Convert.ToDouble(e.Row.Cells[3].Text);
            }
            if (e.Row.Cells[0].Text == "Nett. Profit" || e.Row.Cells[0].Text == "Nett. Loss")
            {
                e.Row.Cells[0].Font.Bold = true;
            }
            if (e.Row.Cells[2].Text == "Nett. Profit" || e.Row.Cells[2].Text == "Nett. Loss")
            {
                e.Row.Cells[2].Font.Bold = true;
            }
            e.Row.Cells[1].Style.Add("border-right", "solid 1px #d6d3ce");
            e.Row.Cells[0].Style.Add("border-bottom", "solid 1px #d6d3ce");
            e.Row.Cells[1].Style.Add("border-bottom", "solid 1px #d6d3ce");
            e.Row.Cells[2].Style.Add("border-bottom", "solid 1px #d6d3ce");
            e.Row.Cells[3].Style.Add("border-bottom", "solid 1px #d6d3ce");

        }
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            e.Row.Cells[0].Text = "Total  ";
            e.Row.Cells[1].Text = Convert.ToDouble(string.Format("{0:0.00}", dblTotalExp)).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "");
            e.Row.Cells[2].Text = "Total  ";
            e.Row.Cells[3].Text = Convert.ToDouble(string.Format("{0:0.00}", dblTotalIncome)).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "");
            e.Row.Font.Bold = true;
            e.Row.Cells[1].HorizontalAlign = HorizontalAlign.Right;
            e.Row.Cells[3].HorizontalAlign = HorizontalAlign.Right;
            e.Row.Cells[1].Style.Add("border-right", "solid 1px #d6d3ce");

        }
    }
    private string _IsFirstYear(string strFinYear)
    {
        string strIsFirstYear = string.Empty;
        strSql = "SELECT MIN(FINYEAR)AS FINYEAR FROM MAS_FINYEAR";
        DataTable DTMinFinyear = DU.getdata(strSql);
        if (DTMinFinyear.Rows.Count > 0)
        {
            if (DTMinFinyear.Rows[0]["FINYEAR"].ToString() != string.Empty)
            {
                if (Convert.ToInt32(DTMinFinyear.Rows[0]["FINYEAR"].ToString()) == Convert.ToInt32(strFinYear))
                {
                    strIsFirstYear = "1";
                }
                else
                {
                    strIsFirstYear = "0";
                }
            }
        }
        return strIsFirstYear;
    }
}